﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace AccesoDatos
{
    public static class BDComida
    {
        public static string connect = "Data Source=.\\sqlexpress; Initial Catalog=BitFood; Integrated Security=true";
      
        
        
        public static void eliminarComidaPlanificada(DateTime fecha, string tipo, string comida)
        {
            SqlConnection SQL_conn = null;
            SqlCommand comando = null;
            int id = BDMenuDiario.getIdMenudiario(fecha, tipo);
            BDComida.eliminarComida(fecha, tipo);

            if (!String.IsNullOrEmpty(comida))
                BDReceta.SetCantidadUsadas(comida, -1);

            try
            {

                SQL_conn = new SqlConnection(connect);
                comando = SQL_conn.CreateCommand();
                comando.CommandText = "DELETE FROM MenuDiario WHERE IdMenuDiario=@id";
                comando.Parameters.AddWithValue("id", id);
                SQL_conn.Open();
                comando.ExecuteNonQuery();

            }
            catch (SqlException)
            {

            }
            finally
            {
                if (SQL_conn != null)
                {
                    SQL_conn.Dispose();

                }
            }

        }
        public static int UltimoIdComida()
        {
            SqlConnection SQL_conn = null;
            SqlCommand comando = null;
            SqlDataReader reader = null;
            int id = -1;
            try
            {
                SQL_conn = new SqlConnection(connect);
                comando = SQL_conn.CreateCommand();
                comando.CommandText = "SELECT IdComida FROM Comida Order by IdComida DESC";
                SQL_conn.Open();
                reader = comando.ExecuteReader();
                if (reader.Read())
                {
                    id = Convert.ToInt32(reader["IdComida"]);
                }
            }
            catch (SqlException)
            {
                return -1;
            }
            finally
            {
                if (SQL_conn != null)
                {
                    SQL_conn.Dispose();
                    if (reader != null)
                        reader.Dispose();

                }
            }
            return id;
        }
        public static void EliminarComida(int id)
        {
            SqlConnection SQL_conn = null;
            SqlCommand comando = null;
            try
            {
                SQL_conn = new SqlConnection(connect);
                comando = SQL_conn.CreateCommand();
                comando.CommandText = "DELETE FROM Comida Where IdComida=@id";
                comando.Parameters.AddWithValue("id", id);
                SQL_conn.Open();
                comando.ExecuteNonQuery();
            }
            catch (Exception)
            {
            }
            finally
            {
                if (SQL_conn != null)
                {
                    SQL_conn.Dispose();
                }
            }
        }
        
        public static void eliminarComida(DateTime fecha, string tipo)
        {
            SqlConnection SQL_conn = null;
            SqlCommand comando = null;
            int id = getIdComida(fecha, tipo);

            try
            {

                SQL_conn = new SqlConnection(connect);
                comando = SQL_conn.CreateCommand();
                comando.CommandText = "DELETE FROM Comida WHERE IdComida=@id";
                comando.Parameters.AddWithValue("id", id);
                SQL_conn.Open();
                comando.ExecuteNonQuery();

            }
            catch (SqlException)
            {

            }
            finally
            {
                if (SQL_conn != null)
                {
                    SQL_conn.Dispose();

                }
            }

        }
        public static string getNombreComida(int IdComida)
        {
            SqlConnection SQL_conn = null;
            SqlCommand comando = null;
            SqlDataReader reader = null;
            string id = null;
            try
            {
                SQL_conn = new SqlConnection(connect);
                comando = SQL_conn.CreateCommand();
                comando.CommandText = "SELECT * FROM Comida WHERE IdComida=@id";
                comando.Parameters.AddWithValue("id",IdComida);
                SQL_conn.Open();
                reader = comando.ExecuteReader();
                if (reader.Read())
                {
                    id = (reader["IdReceta"]).ToString();
                }
            }
            catch (SqlException)
            {
                return null ;
            }
            finally
            {
                if (SQL_conn != null)
                {
                    SQL_conn.Dispose();
                    if (reader != null)
                        reader.Dispose();

                }
            }
            return id;



        }
        public static int getIdComida(DateTime fecha, string tipo)
        {
            SqlConnection SQL_conn = null;
            SqlCommand comando = null;
            SqlDataReader reader = null;
            int id = -1;
            try
            {
                SQL_conn = new SqlConnection(connect);
                comando = SQL_conn.CreateCommand();
                comando.CommandText = "SELECT * FROM Comida,MenuDiario,Receta WHERE Comida.IdComida=MenuDiario.IdComida and Comida.IdReceta = Receta.Nombre and MenuDiario.Fecha = @fecha and Comida.Tipo = @tipo";
                comando.Parameters.AddWithValue("fecha", fecha);
                comando.Parameters.AddWithValue("tipo", tipo);
                SQL_conn.Open();
                reader = comando.ExecuteReader();
                if (reader.Read())
                {
                    id = Convert.ToInt32(reader["IdComida"]);
                }
            }
            catch (SqlException)
            {
                return -1;
            }
            finally
            {
                if (SQL_conn != null)
                {
                    SQL_conn.Dispose();
                    if (reader != null)
                        reader.Dispose();

                }
            }
            return id;



        }
    }

}
